const mysql = require('mysql2/promise');
require('dotenv').config();

// 创建数据库连接池
const pool = mysql.createPool({
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  port: process.env.DB_PORT,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  timezone: '+08:00' // 设置为北京时间
});

// 等待函数 - 用于延迟执行
const wait = (ms) => new Promise(resolve => setTimeout(resolve, ms));

// 带重试机制的数据库连接
async function connectWithRetry(maxRetries = 10, retryInterval = 5000) {
  let retries = 0;
  while (retries < maxRetries) {
    try {
      const connection = await pool.getConnection();
      console.log('数据库连接成功');
      return connection;
    } catch (error) {
      retries++;
      console.error(`数据库连接失败 (${retries}/${maxRetries}):`, error.message);
      if (retries >= maxRetries) {
        throw new Error(`数据库连接失败，已达到最大重试次数: ${maxRetries}`);
      }
      console.log(`等待 ${retryInterval/1000} 秒后重试...`);
      await wait(retryInterval);
    }
  }
}

// 初始化数据库表
async function initDatabase() {
  try {
    // 使用重试机制连接数据库
    const connection = await connectWithRetry();
    
    // 创建股票表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS stocks (
        id INT AUTO_INCREMENT PRIMARY KEY,
        code VARCHAR(20) NOT NULL UNIQUE,
        name VARCHAR(100) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);
    
    // 创建股票价格历史表
    await connection.execute(`
      CREATE TABLE IF NOT EXISTS stock_prices (
        id INT AUTO_INCREMENT PRIMARY KEY,
        stock_id INT NOT NULL,
        price DECIMAL(10, 2) NOT NULL,
        open_price DECIMAL(10, 2),
        close_price DECIMAL(10, 2),
        high_price DECIMAL(10, 2),
        low_price DECIMAL(10, 2),
        yesterday_close_price DECIMAL(10, 2) COMMENT '昨日收盘价',
        volume BIGINT COMMENT '成交量',
        timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (stock_id) REFERENCES stocks(id)
      )
    `);
    
    // 添加新字段（如果表已存在但缺少这些字段）
    try {
      // 检查字段是否存在，如果不存在则添加
      const [columns] = await connection.execute(`
        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'stock_prices' AND COLUMN_NAME = 'yesterday_close_price'
      `, [process.env.DB_NAME]);
      
      if (columns.length === 0) {
        await connection.execute(`
          ALTER TABLE stock_prices 
          ADD COLUMN yesterday_close_price DECIMAL(10, 2) COMMENT '昨日收盘价'
        `);
        console.log('已添加 yesterday_close_price 字段');
      }
      
      const [volumeColumns] = await connection.execute(`
        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'stock_prices' AND COLUMN_NAME = 'volume'
      `, [process.env.DB_NAME]);
      
      if (volumeColumns.length === 0) {
        await connection.execute(`
          ALTER TABLE stock_prices 
          ADD COLUMN volume BIGINT COMMENT '成交量'
        `);
        console.log('已添加 volume 字段');
      }
    } catch (alterError) {
      console.log('添加字段时出错:', alterError.message);
    }
    
    console.log('数据库表初始化成功');
    connection.release();
  } catch (error) {
    console.error('数据库初始化失败:', error);
    throw error; // 重新抛出错误，让调用者知道初始化失败
  }
}

module.exports = {
  pool,
  initDatabase
};